
package cz.geoserver.KMLpublisher.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/** 
 * Class creates database connections and sends SQL commands to DBMS 
 * @author mkepka
 *
 */
public class SQLExecutor {
    private static SQLExecutor SQLExec;
    private static Properties prop;
    
    private static String driver = "org.postgresql.Driver";
    private static String url;
    private static String user;
    private static String pass;     
    private static Connection db;
    
    /**
     * Constructor creates class and checks if connection properties are set
     * @throws SQLException - if an exception occurs while creating new instance of SQLExecutor class 
     */
    private SQLExecutor() throws SQLException{    	
        if(prop==null){
            throw new NullPointerException("Connection properties must be set before getting Connection!");
        }
        
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        db = DriverManager.getConnection(url, user, pass);
    }
    
    /**
     * Method creates or returns instance of SQLExecutor class
     * @return SQLExec - instance of SQLExecutor class
     * @throws SQLException - if an exception occurs while creating new instance of SQLExecutor class 
     */
    public static synchronized SQLExecutor getInstance() throws SQLException {
        if (SQLExec == null) {
            SQLExec = new SQLExecutor();
           
        }
        return SQLExec;
    }
    
    /**
     * Method sends SQL query to DBMS and returns ResultSet
     * @param sql - SQL script as String 
     * @return rs - ResultSet that are returned by DBMS
     */
    public ResultSet executeQuery(String sql) throws SQLException {
        ResultSet rs = null;                  
            Statement stmt = db.createStatement();
            rs = stmt.executeQuery(sql);           
        return rs;
    }
    
    /**
     * Method sends DML commands to DBMS
     * @param sql - SQL command as String
     * @return either (1) the row count for SQL Data Manipulation Language (DML) statements 
     * or (2) 0 for SQL statements that return nothing 
     */
    public int executeUpdate(String sql){
        int res = 0;
        try {
            db = DriverManager.getConnection(url, user, pass);
            Statement stmt = db.createStatement();
            res = stmt.executeUpdate(sql);
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
    
    /**
     * Method sets connection properties from property file
     * @param proper - Property file with connection parameters
     */
    public static void setProperties(Properties proper) {
        prop = proper;
        url = prop.getProperty("Address");
        user = prop.getProperty("Username");
        pass = prop.getProperty("Password");
    }
}